from django.shortcuts import render
from django.http import HttpResponse
from .models import Book,BookOrder,Author,Publisher
from django.db.models import Avg,Sum,Max,Min,F,Q,Count
from django.db import connection
# Create your views here.
#获取所有图书定价的平均价格

def index(request):
    result = Book.objects.aggregate(kangbazi__avg=Avg("price"))
    #返回的是一个字典
    #不会分组
    print(result)
    # print(result.query)
    print(connection.queries)
    return HttpResponse("成功")

#查询每一本书销售的平均价格
def index2(request):
    result = Book.objects.aggregate(avg=Avg('bookorder__price'))
    #select avg(o.price) as '平均价格' from book as b,book_order as o where o.id = b.id;

    print(result)#{'avg': 91.0} 返回的是一个字典
    print(connection.queries)
    print('='*40)
    # books = Book.objects.annotate(avg=Avg('bookorder__price'))
    # #annotate 返回的是一个QuerySet对象  而且在查找的模型上面添加一个属性
    # #增加了一个groupby
    # #只有groupby 才能对每一条数据 求聚合函数的值
    # for book in books:
    #     print("%s/%s"%(book.name,book.avg))
    # print(connection.queries)
    return HttpResponse("成功2")

def index3(request):
    #查询总共有多少本书  也就是book id的数量
    #select count(id) from book
    # result = Book.objects.aggregate(book_num=Count("id"))
    # print(result)
    # print(connection.queries)
    #统计作者表中 有几种不同的邮箱
    # result = Author.objects.aggregate(email_num=Count("email",distinct=True))
    # print(result)
    # print(connection.queries)
    #统计每一本书的销量
    books = Book.objects.annotate(xiaoliang=Count("bookorder"))
    for book in books:
        print('%s/%s' % (book.name,book.xiaoliang))
    print(connection.queries)
    return HttpResponse("成功3")


def index4(request):
    # result = Author.objects.aggregate(max=Max('age'),min=Min('age'))
    # print(result)
    # print(connection.queries)
    result = Book.objects.annotate(max=Max('bookorder__price'), min=Min('bookorder__price'))
    for res in result:
        print("%s/%s/%s" % (res.name,res.max,res.min))
    print(connection.queries)
    return HttpResponse("成功4")

def index5(request):
    #所有图书销售的总额
    # result = BookOrder.objects.aggregate(total=Sum('price'))
    # print(result)
    # print(connection.queries)
    #每一本图书销售的总额
    # result = Book.objects.annotate(total=Sum('bookorder__price'))
    # for res in result:
    #     print("%s/%s" % (res.name,res.total))
    # print(connection.queries)
    #每一本书在2018年度的销售总额
    result = Book.objects.filter(bookorder__create_time__year=2018).annotate(total=Sum('bookorder__price'))
    for res in result:
        print("%s/%s" % (res.name, res.total))
    print(connection.queries)
    return HttpResponse("成功5")

def index6(request):
    # result = Book.objects.update(price=F("price")+10)
    # print(result)
    # print(connection.queries[-1])
    #查询用户中  name 和 email 一样的取出来
    result = Author.objects.filter(name=F('email'))
    print(type(result))
    for res in result:
         print("%s/%s" % (res.name,res.email))
    print(connection.queries[-1])
    return HttpResponse("成功6")


def index7(request):
    # # result = Book.objects.filter(price__gte=100,rating__gte=4.83)
    # # for res in result:
    # #     print("%s/%s/%s" % (res.name, res.price,res.rating))
    # # print(connection.queries[-1])
    # #result = Book.objects.filter(Q(price__lt=100) | Q(rating__lt=4.83))
    # result = Book.objects.filter(Q(price__gte=100)&~Q(name__icontains='传'))
    # for res in result:
    #     print("%s/%s/%s" % (res.name, res.price,res.rating))
    # print(connection.queries[-1])
    # #查询用户中  name 和 email 一样的取出来
    # # result = Author.objects.filter(name=F('email'))
    # # print(type(result))
    # # for res in result:
    # #      print("%s/%s" % (res.name,res.email))
    # # print(connection.queries[-1])
    # book = Book.objects.filter(bookorder__price__gte=85).all()
    # print(book)
    book = Book.objects.get(pk=1)
    print(book.name)
    # results = book.bookorder_set.all() #查询订单为第一本书的
    results = book.laoliu.all() #查询订单为第一本书的
    for re in results:
        print(re.price)


    return HttpResponse("成功7")


